Capstone Project: Create a Customer Segmentation Report for Arvato Financial Services¶

data engineer¶

# import libraries here; add more as necessary
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

import pandas_profiling as pp

# magic word for producing visualizations in notebook
%matplotlib inline

# show all observation-related columns/rows
pd.options.display.max_rows = None

# load in the data
azdias = pd.read_csv('clean_azdias.csv')
customers = pd.read_csv('clean_customers.csv')

df_azdias = azdias.copy()
df_customers = customers.copy()

features = pd.read_csv('clean_features.csv')

features_num = features[features.type=='numeric']
num_columns = features_num['attribute'].to_numpy()
print(len(num_columns))

11

azdias_num = df_azdias[num_columns]
customers_num  = df_customers[num_columns]

pp.ProfileReport(customers_num)

ANZ_HAUSHALTE_AKTIV has 2450 / 1.3% zeros Zeros
ANZ_HH_TITEL has 133454 / 69.6% zeros Zeros
ANZ_KINDER has 132284 / 69.0% zeros Zeros
ANZ_PERSONEN has 7146 / 3.7% zeros Zeros
ANZ_STATISTISCHE_HAUSHALTE is highly correlated with ANZ_HAUSHALTE_AKTIV (ρ = 0.99026) Rejected
ANZ_TITEL has 142316 / 74.3% zeros Zeros
GEBURTSJAHR has 93024 / 48.5% zeros Zeros
MIN_GEBAEUDEJAHR is highly correlated with EINGEFUEGT_AM (ρ = 0.99133) Rejected

pp.ProfileReport(azdias_num)

ANZ_HH_TITEL has 770244 / 86.4% zeros Zeros
ANZ_KINDER has 731242 / 82.0% zeros Zeros
ANZ_PERSONEN has 34103 / 3.8% zeros Zeros
ANZ_STATISTISCHE_HAUSHALTE is highly correlated with ANZ_HAUSHALTE_AKTIV (ρ = 0.97704) Rejected
ANZ_TITEL is highly correlated with ANZ_KINDER (ρ = 0.90846) Rejected
GEBURTSJAHR has 392318 / 44.0% zeros Zeros
MIN_GEBAEUDEJAHR is highly correlated with EINGEFUEGT_AM (ρ = 0.98211) Rejected
Dataset has 108375 duplicate rows Warning

Drop highly correlated columns and impute zeros to median per columns¶

corr_columns = ['ANZ_STATISTISCHE_HAUSHALTE', 'MIN_GEBAEUDEJAHR', 'ANZ_TITEL']

df_azdias.drop(columns=corr_columns, inplace=True)
df_customers.drop(columns=corr_columns, inplace=True)

for col in corr_columns:
    print(col)
    features.drop(features[features.attribute==col].index, inplace=True)
features.shape

ANZ_STATISTISCHE_HAUSHALTE
MIN_GEBAEUDEJAHR
ANZ_TITEL

(279, 5)

columns_to_fill = ['ANZ_HAUSHALTE_AKTIV', 'ANZ_HH_TITEL', 'ANZ_KINDER',
       'ANZ_PERSONEN', 'EINGEFUEGT_AM', 'EINGEZOGENAM_HH_JAHR', 'GEBURTSJAHR',
       'KBA13_ANZAHL_PKW']

df_customers[columns_to_fill] = np.where(np.isclose(df_customers[columns_to_fill].values, 0.0), np.nan, df_customers[columns_to_fill].values)
df_azdias[columns_to_fill] = np.where(np.isclose(df_azdias[columns_to_fill].values, 0.0), np.nan, df_azdias[columns_to_fill].values)

df_azdias[columns_to_fill] = df_azdias[columns_to_fill].fillna(df_azdias[columns_to_fill].median().iloc[0])
df_customers[columns_to_fill] = df_customers[columns_to_fill].fillna(df_customers[columns_to_fill].median().iloc[0])

df_customers[columns_to_fill] = df_customers[columns_to_fill].astype('Int32')
df_azdias[columns_to_fill] = df_azdias[columns_to_fill].astype('Int32')

df_azdias[columns_to_fill].head()

df_customers[columns_to_fill].head()

df_customers.to_csv('clean_customers_zero_median.csv', index=False)
df_azdias.to_csv('clean_azdias_zero_median.csv', index=False)

df_customers[columns_to_fill].describe()

df_azdias[columns_to_fill].describe()

Re-Encoding¶

Our approach will be the following...

Categorical...
    Ordinal Categoricals: Convert to numeric ordinals if necessary and keep the same for simplicity
    Multi-Level Categoricals: One-Hot encode these values
    Binary Categoricals: Convert to numeric binaries ([0,1])

features['type'].value_counts()

ordinal        226
categorical     40
numeric          8
mixed            5
Name: type, dtype: int64

df_cate = {}
dtypes = ['ordinal','categorical', 'mixed']
for d in dtypes:
    df_cate[d]=features[features['type']==d]['attribute']

Ordinal¶

def check_unique_vals(df):
    for col in df.columns:
        values = list(df[col].unique())
        print(f'{col}: {values}')

df_cate['ordinal'].shape

(226,)

df_azdias[df_cate['ordinal']].head()

check_unique_vals(df_azdias[df_cate['ordinal']])

ARBEIT: [4.0, 3.0, 2.0, 1.0, 5.0, 9.0]
BALLRAUM: [6.0, 2.0, 4.0, 3.0, 7.0, 1.0, 5.0]
D19_KONSUMTYP_MAX: [9.0, 8.0, 1.0, 2.0, 3.0, 4.0]
EWDICHTE: [6.0, 3.0, 4.0, 2.0, 5.0, 1.0]
FINANZ_ANLEGER: [5.0, 2.0, 1.0, 4.0, 3.0]
FINANZ_HAUSBAUER: [3.0, 5.0, 2.0, 4.0, 1.0]
FINANZ_MINIMALIST: [3.0, 1.0, 4.0, 2.0, 5.0]
FINANZ_SPARER: [4.0, 5.0, 2.0, 3.0, 1.0]
FINANZ_UNAUFFAELLIGER: [5.0, 4.0, 3.0, 1.0, 2.0]
FINANZ_VORSORGER: [3.0, 2.0, 1.0, 5.0, 4.0]
GEBAEUDETYP_RASTER: [4.0, 3.0, 5.0, 1.0, 2.0]
HEALTH_TYP: [3.0, 2.0, 1.0]
HH_EINKOMMEN_SCORE: [2.0, 6.0, 4.0, 1.0, 5.0, 3.0]
INNENSTADT: [5.0, 8.0, 4.0, 6.0, 1.0, 7.0, 3.0, 2.0]
KBA05_ALTER1: [2.0, 3.0, 0.0, 1.0, 4.0]
KBA05_ALTER2: [3.0, 4.0, 5.0, 2.0, 1.0]
KBA05_ALTER3: [3.0, 1.0, 4.0, 2.0, 5.0]
KBA05_ALTER4: [3.0, 4.0, 0.0, 5.0, 2.0, 1.0]
KBA05_ANHANG: [1.0, 0.0, 2.0, 3.0]
KBA05_ANTG1: [0.0, 1.0, 4.0, 2.0, 3.0]
KBA05_ANTG2: [0.0, 3.0, 1.0, 4.0, 2.0]
KBA05_ANTG3: [0.0, 1.0, 2.0, 3.0]
KBA05_ANTG4: [0.0, 2.0, 1.0]
KBA05_AUTOQUOT: [3.0, 1.0, 4.0, 2.0, 5.0]
KBA05_CCM1: [3.0, 1.0, 5.0, 2.0, 4.0]
KBA05_CCM2: [3.0, 5.0, 2.0, 1.0, 4.0]
KBA05_CCM3: [3.0, 1.0, 5.0, 4.0, 2.0]
KBA05_CCM4: [0.0, 4.0, 1.0, 2.0, 3.0]
KBA05_DIESEL: [2.0, 0.0, 3.0, 1.0, 4.0]
KBA05_FRAU: [3.0, 4.0, 5.0, 2.0, 1.0]
KBA05_GBZ: [3.0, 1.0, 4.0, 5.0, 2.0]
KBA05_HERST1: [2.0, 5.0, 4.0, 3.0, 0.0, 1.0]
KBA05_HERST2: [3.0, 5.0, 2.0, 4.0, 1.0]
KBA05_HERST3: [3.0, 2.0, 4.0, 1.0, 0.0, 5.0]
KBA05_HERST4: [3.0, 2.0, 1.0, 4.0, 0.0, 5.0]
KBA05_HERST5: [3.0, 0.0, 5.0, 4.0, 2.0, 1.0]
KBA05_HERSTTEMP: [3.0, 4.0, 1.0, 2.0, 5.0]
KBA05_KRSAQUOT: [3.0, 1.0, 4.0, 2.0, 5.0]
KBA05_KRSHERST1: [3.0, 5.0, 4.0, 2.0, 1.0]
KBA05_KRSHERST2: [3.0, 4.0, 2.0, 5.0, 1.0]
KBA05_KRSHERST3: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA05_KRSKLEIN: [2.0, 1.0, 3.0]
KBA05_KRSOBER: [2.0, 1.0, 3.0]
KBA05_KRSVAN: [2.0, 1.0, 3.0]
KBA05_KRSZUL: [2.0, 3.0, 1.0]
KBA05_KW1: [3.0, 1.0, 4.0, 2.0, 5.0]
KBA05_KW2: [3.0, 2.0, 4.0, 5.0, 1.0]
KBA05_KW3: [1.0, 4.0, 2.0, 3.0, 0.0]
KBA05_MAXAH: [3.0, 2.0, 5.0, 1.0, 4.0]
KBA05_MAXBJ: [1.0, 4.0, 2.0, 3.0]
KBA05_MAXHERST: [2.0, 5.0, 3.0, 1.0, 4.0]
KBA05_MAXSEG: [2.0, 4.0, 1.0, 3.0]
KBA05_MAXVORB: [2.0, 3.0, 1.0]
KBA05_MOD1: [0.0, 3.0, 2.0, 1.0, 4.0]
KBA05_MOD2: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA05_MOD3: [3.0, 2.0, 5.0, 4.0, 1.0]
KBA05_MOD4: [3.0, 0.0, 1.0, 2.0, 4.0, 5.0]
KBA05_MOD8: [0.0, 1.0, 2.0, 3.0]
KBA05_MODTEMP: [3.0, 1.0, 4.0, 5.0, 2.0, 6.0]
KBA05_MOTOR: [3.0, 1.0, 4.0, 2.0]
KBA05_MOTRAD: [1.0, 0.0, 3.0, 2.0]
KBA05_SEG1: [1.0, 0.0, 2.0, 3.0]
KBA05_SEG10: [2.0, 4.0, 1.0, 3.0, 0.0]
KBA05_SEG2: [3.0, 1.0, 5.0, 2.0, 4.0]
KBA05_SEG3: [3.0, 2.0, 5.0, 1.0, 4.0]
KBA05_SEG4: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA05_SEG5: [1.0, 2.0, 0.0, 3.0, 4.0]
KBA05_SEG6: [0.0, 1.0]
KBA05_SEG7: [0.0, 3.0, 1.0, 2.0]
KBA05_SEG8: [0.0, 3.0, 1.0, 2.0]
KBA05_SEG9: [0.0, 1.0, 2.0, 3.0]
KBA05_VORB0: [3.0, 1.0, 4.0, 2.0, 5.0]
KBA05_VORB1: [3.0, 1.0, 2.0, 5.0, 4.0]
KBA05_VORB2: [3.0, 5.0, 1.0, 4.0, 2.0, 0.0]
KBA05_ZUL1: [3.0, 5.0, 2.0, 4.0, 1.0]
KBA05_ZUL2: [3.0, 1.0, 4.0, 2.0, 5.0]
KBA05_ZUL3: [3.0, 0.0, 4.0, 2.0, 1.0, 5.0]
KBA05_ZUL4: [2.0, 4.0, 3.0, 5.0, 1.0, 0.0]
KBA13_ALTERHALTER_30: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA13_ALTERHALTER_45: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA13_ALTERHALTER_60: [3.0, 5.0, 2.0, 4.0, 1.0]
KBA13_ALTERHALTER_61: [3.0, 4.0, 2.0, 5.0, 1.0]
KBA13_ANTG1: [2.0, 1.0, 4.0, 3.0, 0.0]
KBA13_ANTG2: [3.0, 4.0, 2.0, 1.0, 0.0]
KBA13_ANTG3: [2.0, 1.0, 0.0, 3.0]
KBA13_ANTG4: [0.0, 1.0, 2.0]
KBA13_AUDI: [3.0, 4.0, 5.0, 2.0, 1.0]
KBA13_AUTOQUOTE: [3.0, 2.0, 4.0, 5.0, 1.0, 0.0]
KBA13_BAUMAX: [1.0, 2.0, 4.0, 5.0, 3.0]
KBA13_BJ_1999: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA13_BJ_2000: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA13_BJ_2004: [3.0, 4.0, 2.0, 1.0, 5.0]
KBA13_BJ_2006: [3.0, 5.0, 2.0, 4.0, 1.0]
KBA13_BJ_2008: [3.0, 4.0, 0.0, 2.0, 5.0, 1.0]
KBA13_BJ_2009: [3.0, 2.0, 1.0, 5.0, 0.0, 4.0]
KBA13_BMW: [3.0, 4.0, 2.0, 5.0, 1.0]
KBA13_CCM_0_1400: [3.0, 2.0, 1.0, 5.0, 0.0, 4.0]
KBA13_CCM_1000: [3.0, 0.0, 1.0, 4.0, 5.0, 2.0]
KBA13_CCM_1200: [3.0, 0.0, 2.0, 1.0, 4.0, 5.0]
KBA13_CCM_1400: [3.0, 4.0, 2.0, 1.0, 5.0]
KBA13_CCM_1401_2500: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA13_CCM_1500: [1.0, 4.0, 3.0, 5.0, 2.0]
KBA13_CCM_1600: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA13_CCM_1800: [3.0, 2.0, 4.0, 5.0, 1.0, 0.0]
KBA13_CCM_2000: [3.0, 5.0, 4.0, 2.0, 1.0]
KBA13_CCM_2500: [3.0, 4.0, 0.0, 5.0, 2.0, 1.0]
KBA13_CCM_2501: [3.0, 4.0, 5.0, 0.0, 2.0, 1.0]
KBA13_CCM_3000: [3.0, 0.0, 5.0, 2.0, 1.0, 4.0]
KBA13_CCM_3001: [1.0, 5.0, 4.0, 3.0, 2.0]
KBA13_FAB_ASIEN: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA13_FAB_SONSTIGE: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA13_FIAT: [3.0, 4.0, 5.0, 2.0, 1.0]
KBA13_FORD: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA13_GBZ: [3.0, 4.0, 5.0, 2.0, 1.0]
KBA13_HALTER_20: [3.0, 2.0, 4.0, 5.0, 1.0]
KBA13_HALTER_25: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA13_HALTER_30: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA13_HALTER_35: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA13_HALTER_40: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA13_HALTER_45: [3.0, 2.0, 5.0, 1.0, 4.0]
KBA13_HALTER_50: [3.0, 2.0, 5.0, 4.0, 1.0]
KBA13_HALTER_55: [3.0, 5.0, 4.0, 2.0, 1.0]
KBA13_HALTER_60: [3.0, 4.0, 2.0, 5.0, 1.0]
KBA13_HALTER_65: [3.0, 4.0, 5.0, 2.0, 1.0]
KBA13_HALTER_66: [3.0, 4.0, 2.0, 5.0, 1.0]
KBA13_HERST_ASIEN: [3.0, 1.0, 4.0, 2.0, 5.0]
KBA13_HERST_AUDI_VW: [3.0, 4.0, 2.0, 5.0, 1.0]
KBA13_HERST_BMW_BENZ: [3.0, 4.0, 5.0, 2.0, 1.0]
KBA13_HERST_EUROPA: [3.0, 4.0, 2.0, 5.0, 1.0]
KBA13_HERST_FORD_OPEL: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA13_HERST_SONST: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA13_HHZ: [3.0, 5.0, 4.0, 2.0, 1.0]
KBA13_KMH_0_140: [3.0, 1.0, 5.0, 0.0, 4.0, 2.0]
KBA13_KMH_110: [1.0, 2.0, 3.0]
KBA13_KMH_140: [1.0, 3.0, 5.0, 2.0, 4.0]
KBA13_KMH_140_210: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA13_KMH_180: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA13_KMH_210: [3.0, 4.0, 2.0, 5.0, 1.0]
KBA13_KMH_211: [3.0, 4.0, 5.0, 2.0, 1.0, 0.0]
KBA13_KMH_250: [3.0, 4.0, 5.0, 2.0, 1.0, 0.0]
KBA13_KMH_251: [1.0, 3.0, 2.0]
KBA13_KRSAQUOT: [3.0, 2.0, 4.0, 1.0, 5.0, 0.0]
KBA13_KRSHERST_AUDI_VW: [3.0, 4.0, 2.0, 1.0, 5.0, 0.0]
KBA13_KRSHERST_BMW_BENZ: [3.0, 4.0, 5.0, 2.0, 1.0, 0.0]
KBA13_KRSHERST_FORD_OPEL: [3.0, 2.0, 4.0, 1.0, 5.0, 0.0]
KBA13_KRSSEG_KLEIN: [2.0, 3.0, 1.0, 0.0]
KBA13_KRSSEG_OBER: [2.0, 3.0, 1.0, 0.0]
KBA13_KRSSEG_VAN: [2.0, 1.0, 3.0, 0.0]
KBA13_KRSZUL_NEU: [2.0, 1.0, 3.0, 0.0]
KBA13_KW_0_60: [3.0, 1.0, 2.0, 4.0, 5.0]
KBA13_KW_110: [3.0, 4.0, 1.0, 5.0, 0.0, 2.0]
KBA13_KW_120: [3.0, 4.0, 1.0, 5.0, 0.0, 2.0]
KBA13_KW_121: [3.0, 4.0, 5.0, 2.0, 0.0, 1.0]
KBA13_KW_30: [1.0, 2.0, 3.0]
KBA13_KW_40: [3.0, 2.0, 1.0, 4.0, 5.0, 0.0]
KBA13_KW_50: [3.0, 4.0, 2.0, 0.0, 1.0, 5.0]
KBA13_KW_60: [3.0, 0.0, 1.0, 2.0, 4.0, 5.0]
KBA13_KW_61_120: [3.0, 5.0, 2.0, 4.0, 1.0]
KBA13_KW_70: [3.0, 1.0, 4.0, 2.0, 0.0, 5.0]
KBA13_KW_80: [3.0, 2.0, 4.0, 1.0, 0.0, 5.0]
KBA13_KW_90: [3.0, 2.0, 0.0, 4.0, 1.0, 5.0]
KBA13_MAZDA: [3.0, 2.0, 4.0, 5.0, 1.0]
KBA13_MERCEDES: [3.0, 4.0, 5.0, 2.0, 1.0]
KBA13_MOTOR: [3.0, 4.0, 2.0, 1.0]
KBA13_NISSAN: [3.0, 2.0, 5.0, 4.0, 1.0]
KBA13_OPEL: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA13_PEUGEOT: [3.0, 4.0, 2.0, 1.0, 5.0]
KBA13_RENAULT: [3.0, 2.0, 4.0, 5.0, 1.0]
KBA13_SEG_GELAENDEWAGEN: [3.0, 2.0, 5.0, 4.0, 1.0]
KBA13_SEG_GROSSRAUMVANS: [3.0, 4.0, 1.0, 5.0, 2.0]
KBA13_SEG_KLEINST: [3.0, 2.0, 5.0, 4.0, 1.0]
KBA13_SEG_KLEINWAGEN: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA13_SEG_KOMPAKTKLASSE: [3.0, 5.0, 1.0, 4.0, 2.0]
KBA13_SEG_MINIVANS: [3.0, 4.0, 2.0, 1.0, 5.0]
KBA13_SEG_MINIWAGEN: [3.0, 2.0, 5.0, 4.0, 1.0]
KBA13_SEG_MITTELKLASSE: [3.0, 2.0, 4.0, 5.0, 1.0]
KBA13_SEG_OBEREMITTELKLASSE: [3.0, 4.0, 5.0, 2.0, 1.0]
KBA13_SEG_OBERKLASSE: [3.0, 1.0, 4.0, 5.0, 0.0, 2.0]
KBA13_SEG_SONSTIGE: [3.0, 2.0, 5.0, 4.0, 1.0]
KBA13_SEG_SPORTWAGEN: [3.0, 4.0, 5.0, 1.0, 2.0, 0.0]
KBA13_SEG_UTILITIES: [3.0, 5.0, 2.0, 4.0, 1.0]
KBA13_SEG_VAN: [3.0, 4.0, 2.0, 1.0, 5.0]
KBA13_SEG_WOHNMOBILE: [3.0, 2.0, 5.0, 4.0, 0.0, 1.0]
KBA13_SITZE_4: [3.0, 4.0, 5.0, 2.0, 1.0]
KBA13_SITZE_5: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA13_SITZE_6: [3.0, 4.0, 2.0, 5.0, 1.0]
KBA13_TOYOTA: [3.0, 2.0, 4.0, 5.0, 1.0]
KBA13_VORB_0: [3.0, 4.0, 5.0, 2.0, 1.0]
KBA13_VORB_1: [3.0, 4.0, 2.0, 1.0, 5.0]
KBA13_VORB_1_2: [3.0, 4.0, 2.0, 1.0, 5.0]
KBA13_VORB_2: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA13_VORB_3: [3.0, 2.0, 4.0, 0.0, 1.0, 5.0]
KBA13_VW: [3.0, 4.0, 2.0, 5.0, 1.0]
KONSUMNAEHE: [1.0, 5.0, 4.0, 3.0, 2.0, 6.0, 7.0]
MOBI_REGIO: [1.0, 3.0, 4.0, 5.0, 2.0, 6.0]
ONLINE_AFFINITAET: [1.0, 3.0, 2.0, 5.0, 4.0, 0.0]
ORTSGR_KLS9: [5.0, 3.0, 6.0, 4.0, 8.0, 2.0, 7.0, 9.0, 1.0, 0.0]
PLZ8_ANTG1: [2.0, 3.0, 1.0, 4.0, 0.0]
PLZ8_ANTG2: [3.0, 2.0, 4.0, 1.0, 0.0]
PLZ8_ANTG3: [2.0, 1.0, 3.0, 0.0]
PLZ8_ANTG4: [0.0, 1.0, 2.0]
PLZ8_GBZ: [3.0, 4.0, 5.0, 2.0, 1.0]
PLZ8_HHZ: [3.0, 5.0, 4.0, 2.0, 1.0]
REGIOTYP: [6.0, 3.0, 2.0, 0.0, 5.0, 1.0, 7.0, 4.0]
RELAT_AB: [3.0, 4.0, 2.0, 5.0, 1.0, 9.0]
RETOURTYP_BK_S: [5.0, 1.0, 3.0, 2.0, 4.0]
RT_KEIN_ANREIZ: [1.0, 5.0, 3.0, 4.0, 2.0]
RT_SCHNAEPPCHEN: [4.0, 3.0, 2.0, 5.0, 1.0]
RT_UEBERGROESSE: [1.0, 5.0, 3.0, 4.0, 2.0]
SEMIO_DOM: [6.0, 7.0, 4.0, 2.0, 1.0, 5.0, 3.0]
SEMIO_ERL: [3.0, 2.0, 6.0, 7.0, 4.0, 5.0, 1.0]
SEMIO_FAM: [6.0, 4.0, 1.0, 5.0, 7.0, 2.0, 3.0]
SEMIO_KAEM: [6.0, 4.0, 7.0, 5.0, 2.0, 3.0, 1.0]
SEMIO_KRIT: [7.0, 4.0, 3.0, 1.0, 5.0, 6.0, 2.0]
SEMIO_KULT: [3.0, 4.0, 6.0, 5.0, 7.0, 1.0, 2.0]
SEMIO_LUST: [5.0, 2.0, 4.0, 6.0, 7.0, 3.0, 1.0]
SEMIO_MAT: [5.0, 3.0, 1.0, 2.0, 4.0, 7.0, 6.0]
SEMIO_PFLICHT: [5.0, 7.0, 3.0, 4.0, 1.0, 6.0, 2.0]
SEMIO_RAT: [4.0, 6.0, 3.0, 2.0, 7.0, 5.0, 1.0]
SEMIO_REL: [7.0, 4.0, 3.0, 2.0, 5.0, 1.0, 6.0]
SEMIO_SOZ: [2.0, 5.0, 4.0, 6.0, 7.0, 3.0, 1.0]
SEMIO_TRADV: [3.0, 6.0, 4.0, 2.0, 7.0, 5.0, 1.0]
SEMIO_VERT: [1.0, 4.0, 7.0, 2.0, 6.0, 5.0, 3.0]
W_KEIT_KIND_HH: [6.0, 3.0, 2.0, 5.0, 4.0, 1.0]
WOHNDAUER_2008: [9.0, 8.0, 3.0, 4.0, 5.0, 6.0, 2.0, 7.0, 1.0]
ALTERSKATEGORIE_GROB: [2, 1, 3, 4, 9]

check_unique_vals(df_customers[df_cate['ordinal']])

ARBEIT: [1.0, 3.0, 2.0, 4.0, 5.0, 9.0]
BALLRAUM: [3.0, 6.0, 7.0, 5.0, 2.0, 1.0, 4.0]
D19_KONSUMTYP_MAX: [2.0, 3.0, 4.0, 1.0, 8.0, 9.0]
EWDICHTE: [2.0, 5.0, 4.0, 1.0, 3.0, 6.0]
FINANZ_ANLEGER: [1.0, 2.0, 4.0, 3.0, 5.0]
FINANZ_HAUSBAUER: [2.0, 4.0, 3.0, 5.0, 1.0]
FINANZ_MINIMALIST: [5.0, 3.0, 2.0, 4.0, 1.0]
FINANZ_SPARER: [1.0, 2.0, 3.0, 4.0, 5.0]
FINANZ_UNAUFFAELLIGER: [2.0, 3.0, 4.0, 1.0, 5.0]
FINANZ_VORSORGER: [5.0, 4.0, 1.0, 2.0, 3.0]
GEBAEUDETYP_RASTER: [4.0, 3.0, 2.0, 5.0, 1.0]
HEALTH_TYP: [1.0, 2.0, 3.0]
HH_EINKOMMEN_SCORE: [1.0, 2.0, 4.0, 6.0, 3.0, 5.0]
INNENSTADT: [4.0, 5.0, 1.0, 7.0, 8.0, 6.0, 2.0, 3.0]
KBA05_ALTER1: [2.0, 1.0, 0.0, 3.0, 4.0]
KBA05_ALTER2: [2.0, 3.0, 4.0, 1.0, 5.0]
KBA05_ALTER3: [4.0, 3.0, 2.0, 1.0, 5.0]
KBA05_ALTER4: [4.0, 3.0, 1.0, 5.0, 2.0, 0.0]
KBA05_ANHANG: [1.0, 3.0, 0.0, 2.0]
KBA05_ANTG1: [2.0, 3.0, 0.0, 4.0, 1.0]
KBA05_ANTG2: [2.0, 0.0, 3.0, 1.0, 4.0]
KBA05_ANTG3: [0.0, 2.0, 1.0, 3.0]
KBA05_ANTG4: [0.0, 2.0, 1.0]
KBA05_AUTOQUOT: [5.0, 4.0, 3.0, 1.0, 2.0]
KBA05_CCM1: [3.0, 2.0, 5.0, 4.0, 1.0]
KBA05_CCM2: [3.0, 2.0, 4.0, 5.0, 1.0]
KBA05_CCM3: [3.0, 5.0, 2.0, 4.0, 1.0]
KBA05_CCM4: [1.0, 2.0, 0.0, 4.0, 3.0]
KBA05_DIESEL: [1.0, 2.0, 4.0, 3.0, 0.0]
KBA05_FRAU: [4.0, 3.0, 2.0, 5.0, 1.0]
KBA05_GBZ: [4.0, 3.0, 1.0, 5.0, 2.0]
KBA05_HERST1: [4.0, 2.0, 3.0, 1.0, 5.0, 0.0]
KBA05_HERST2: [3.0, 4.0, 1.0, 2.0, 5.0]
KBA05_HERST3: [3.0, 1.0, 2.0, 0.0, 4.0, 5.0]
KBA05_HERST4: [2.0, 3.0, 5.0, 1.0, 4.0, 0.0]
KBA05_HERST5: [2.0, 3.0, 1.0, 0.0, 4.0, 5.0]
KBA05_HERSTTEMP: [2.0, 3.0, 1.0, 4.0, 5.0]
KBA05_KRSAQUOT: [4.0, 3.0, 2.0, 1.0, 5.0]
KBA05_KRSHERST1: [4.0, 3.0, 2.0, 5.0, 1.0]
KBA05_KRSHERST2: [3.0, 4.0, 2.0, 1.0, 5.0]
KBA05_KRSHERST3: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA05_KRSKLEIN: [2.0, 1.0, 3.0]
KBA05_KRSOBER: [3.0, 2.0, 1.0]
KBA05_KRSVAN: [3.0, 2.0, 1.0]
KBA05_KRSZUL: [2.0, 3.0, 1.0]
KBA05_KW1: [3.0, 2.0, 5.0, 4.0, 1.0]
KBA05_KW2: [3.0, 4.0, 2.0, 1.0, 5.0]
KBA05_KW3: [3.0, 1.0, 0.0, 4.0, 2.0]
KBA05_MAXAH: [5.0, 3.0, 4.0, 2.0, 1.0]
KBA05_MAXBJ: [2.0, 4.0, 1.0, 3.0]
KBA05_MAXHERST: [2.0, 4.0, 3.0, 1.0, 5.0]
KBA05_MAXSEG: [3.0, 2.0, 1.0, 4.0]
KBA05_MAXVORB: [2.0, 1.0, 3.0]
KBA05_MOD1: [2.0, 0.0, 1.0, 4.0, 3.0]
KBA05_MOD2: [4.0, 3.0, 5.0, 2.0, 1.0]
KBA05_MOD3: [3.0, 4.0, 5.0, 1.0, 2.0]
KBA05_MOD4: [4.0, 3.0, 0.0, 1.0, 2.0, 5.0]
KBA05_MOD8: [2.0, 1.0, 0.0, 3.0]
KBA05_MODTEMP: [2.0, 3.0, 4.0, 1.0, 5.0, 6.0]
KBA05_MOTOR: [4.0, 3.0, 2.0, 1.0]
KBA05_MOTRAD: [1.0, 0.0, 2.0, 3.0]
KBA05_SEG1: [1.0, 3.0, 0.0, 2.0]
KBA05_SEG10: [2.0, 4.0, 1.0, 3.0, 0.0]
KBA05_SEG2: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA05_SEG3: [2.0, 3.0, 4.0, 5.0, 1.0]
KBA05_SEG4: [3.0, 4.0, 2.0, 5.0, 1.0]
KBA05_SEG5: [3.0, 1.0, 0.0, 4.0, 2.0]
KBA05_SEG6: [1.0, 0.0]
KBA05_SEG7: [0.0, 3.0, 1.0, 2.0]
KBA05_SEG8: [1.0, 0.0, 2.0, 3.0]
KBA05_SEG9: [2.0, 1.0, 0.0, 3.0]
KBA05_VORB0: [4.0, 3.0, 2.0, 5.0, 1.0]
KBA05_VORB1: [3.0, 4.0, 1.0, 2.0, 5.0]
KBA05_VORB2: [2.0, 3.0, 1.0, 0.0, 4.0, 5.0]
KBA05_ZUL1: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA05_ZUL2: [4.0, 3.0, 5.0, 2.0, 1.0]
KBA05_ZUL3: [2.0, 3.0, 4.0, 5.0, 0.0, 1.0]
KBA05_ZUL4: [3.0, 2.0, 4.0, 1.0, 0.0, 5.0]
KBA13_ALTERHALTER_30: [1.0, 3.0, 2.0, 4.0, 5.0]
KBA13_ALTERHALTER_45: [2.0, 3.0, 4.0, 5.0, 1.0]
KBA13_ALTERHALTER_60: [5.0, 3.0, 2.0, 1.0, 4.0]
KBA13_ALTERHALTER_61: [3.0, 4.0, 2.0, 1.0, 5.0]
KBA13_ANTG1: [3.0, 2.0, 1.0, 4.0, 0.0]
KBA13_ANTG2: [3.0, 4.0, 2.0, 1.0, 0.0]
KBA13_ANTG3: [1.0, 3.0, 0.0, 2.0]
KBA13_ANTG4: [0.0, 1.0, 2.0]
KBA13_AUDI: [4.0, 3.0, 2.0, 1.0, 5.0]
KBA13_AUTOQUOTE: [4.0, 3.0, 2.0, 1.0, 5.0]
KBA13_BAUMAX: [1.0, 3.0, 2.0, 5.0, 4.0]
KBA13_BJ_1999: [3.0, 4.0, 1.0, 2.0, 5.0]
KBA13_BJ_2000: [2.0, 3.0, 1.0, 4.0, 5.0]
KBA13_BJ_2004: [3.0, 1.0, 2.0, 4.0, 5.0]
KBA13_BJ_2006: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA13_BJ_2008: [4.0, 3.0, 0.0, 5.0, 2.0, 1.0]
KBA13_BJ_2009: [3.0, 5.0, 2.0, 1.0, 4.0, 0.0]
KBA13_BMW: [5.0, 3.0, 4.0, 2.0, 1.0]
KBA13_CCM_0_1400: [2.0, 3.0, 4.0, 0.0, 5.0, 1.0]
KBA13_CCM_1000: [3.0, 5.0, 2.0, 0.0, 1.0, 4.0]
KBA13_CCM_1200: [2.0, 3.0, 0.0, 4.0, 5.0, 1.0]
KBA13_CCM_1400: [3.0, 4.0, 2.0, 1.0, 5.0]
KBA13_CCM_1401_2500: [4.0, 3.0, 1.0, 2.0, 5.0]
KBA13_CCM_1500: [4.0, 1.0, 3.0, 2.0, 5.0]
KBA13_CCM_1600: [2.0, 3.0, 4.0, 5.0, 1.0]
KBA13_CCM_1800: [3.0, 2.0, 0.0, 1.0, 4.0, 5.0]
KBA13_CCM_2000: [4.0, 3.0, 2.0, 5.0, 1.0]
KBA13_CCM_2500: [4.0, 3.0, 2.0, 0.0, 5.0, 1.0]
KBA13_CCM_2501: [0.0, 3.0, 4.0, 5.0, 2.0, 1.0]
KBA13_CCM_3000: [3.0, 5.0, 4.0, 2.0, 0.0, 1.0]
KBA13_CCM_3001: [3.0, 1.0, 4.0, 5.0, 2.0]
KBA13_FAB_ASIEN: [2.0, 3.0, 4.0, 1.0, 5.0]
KBA13_FAB_SONSTIGE: [3.0, 2.0, 5.0, 4.0, 1.0]
KBA13_FIAT: [3.0, 1.0, 2.0, 4.0, 5.0]
KBA13_FORD: [3.0, 4.0, 2.0, 5.0, 1.0]
KBA13_GBZ: [5.0, 3.0, 2.0, 4.0, 1.0]
KBA13_HALTER_20: [1.0, 3.0, 4.0, 2.0, 5.0]
KBA13_HALTER_25: [1.0, 3.0, 4.0, 2.0, 5.0]
KBA13_HALTER_30: [1.0, 3.0, 4.0, 2.0, 5.0]
KBA13_HALTER_35: [3.0, 4.0, 2.0, 5.0, 1.0]
KBA13_HALTER_40: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA13_HALTER_45: [2.0, 3.0, 4.0, 5.0, 1.0]
KBA13_HALTER_50: [4.0, 3.0, 2.0, 5.0, 1.0]
KBA13_HALTER_55: [5.0, 3.0, 2.0, 4.0, 1.0]
KBA13_HALTER_60: [5.0, 3.0, 2.0, 1.0, 4.0]
KBA13_HALTER_65: [4.0, 3.0, 1.0, 2.0, 5.0]
KBA13_HALTER_66: [3.0, 4.0, 2.0, 1.0, 5.0]
KBA13_HERST_ASIEN: [2.0, 3.0, 4.0, 1.0, 5.0]
KBA13_HERST_AUDI_VW: [4.0, 3.0, 1.0, 2.0, 5.0]
KBA13_HERST_BMW_BENZ: [4.0, 3.0, 5.0, 2.0, 1.0]
KBA13_HERST_EUROPA: [2.0, 3.0, 4.0, 5.0, 1.0]
KBA13_HERST_FORD_OPEL: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA13_HERST_SONST: [3.0, 2.0, 5.0, 4.0, 1.0]
KBA13_HHZ: [5.0, 3.0, 4.0, 2.0, 1.0]
KBA13_KMH_0_140: [3.0, 4.0, 1.0, 0.0, 2.0, 5.0]
KBA13_KMH_110: [2.0, 1.0, 3.0]
KBA13_KMH_140: [4.0, 1.0, 5.0, 3.0, 2.0]
KBA13_KMH_140_210: [3.0, 2.0, 1.0, 4.0, 5.0]
KBA13_KMH_180: [2.0, 3.0, 4.0, 1.0, 5.0]
KBA13_KMH_210: [4.0, 3.0, 2.0, 5.0, 1.0]
KBA13_KMH_211: [3.0, 5.0, 4.0, 2.0, 0.0, 1.0]
KBA13_KMH_250: [3.0, 5.0, 4.0, 2.0, 0.0, 1.0]
KBA13_KMH_251: [1.0, 3.0, 2.0]
KBA13_KRSAQUOT: [3.0, 4.0, 5.0, 2.0, 1.0]
KBA13_KRSHERST_AUDI_VW: [4.0, 3.0, 2.0, 5.0, 1.0]
KBA13_KRSHERST_BMW_BENZ: [3.0, 5.0, 2.0, 4.0, 1.0]
KBA13_KRSHERST_FORD_OPEL: [3.0, 1.0, 2.0, 4.0, 5.0]
KBA13_KRSSEG_KLEIN: [2.0, 1.0, 3.0]
KBA13_KRSSEG_OBER: [2.0, 1.0, 3.0, 0.0]
KBA13_KRSSEG_VAN: [2.0, 1.0, 3.0, 0.0]
KBA13_KRSZUL_NEU: [3.0, 2.0, 1.0, 0.0]
KBA13_KW_0_60: [3.0, 4.0, 2.0, 1.0, 5.0]
KBA13_KW_110: [4.0, 3.0, 2.0, 0.0, 1.0, 5.0]
KBA13_KW_120: [3.0, 1.0, 5.0, 4.0, 0.0, 2.0]
KBA13_KW_121: [3.0, 4.0, 5.0, 0.0, 1.0, 2.0]
KBA13_KW_30: [2.0, 1.0, 3.0]
KBA13_KW_40: [3.0, 2.0, 1.0, 0.0, 4.0, 5.0]
KBA13_KW_50: [2.0, 3.0, 4.0, 0.0, 5.0, 1.0]
KBA13_KW_60: [3.0, 4.0, 2.0, 0.0, 1.0, 5.0]
KBA13_KW_61_120: [3.0, 1.0, 4.0, 5.0, 2.0]
KBA13_KW_70: [3.0, 2.0, 0.0, 4.0, 5.0, 1.0]
KBA13_KW_80: [2.0, 3.0, 1.0, 5.0, 0.0, 4.0]
KBA13_KW_90: [3.0, 0.0, 2.0, 4.0, 5.0, 1.0]
KBA13_MAZDA: [3.0, 5.0, 2.0, 4.0, 1.0]
KBA13_MERCEDES: [3.0, 4.0, 5.0, 2.0, 1.0]
KBA13_MOTOR: [4.0, 3.0, 1.0, 2.0]
KBA13_NISSAN: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA13_OPEL: [3.0, 1.0, 4.0, 2.0, 5.0]
KBA13_PEUGEOT: [2.0, 3.0, 5.0, 4.0, 1.0]
KBA13_RENAULT: [2.0, 3.0, 4.0, 1.0, 5.0]
KBA13_SEG_GELAENDEWAGEN: [3.0, 4.0, 5.0, 2.0, 1.0]
KBA13_SEG_GROSSRAUMVANS: [5.0, 3.0, 4.0, 2.0, 1.0]
KBA13_SEG_KLEINST: [2.0, 3.0, 4.0, 1.0, 5.0]
KBA13_SEG_KLEINWAGEN: [2.0, 3.0, 5.0, 1.0, 4.0]
KBA13_SEG_KOMPAKTKLASSE: [3.0, 1.0, 2.0, 5.0, 4.0]
KBA13_SEG_MINIVANS: [3.0, 2.0, 4.0, 5.0, 1.0]
KBA13_SEG_MINIWAGEN: [1.0, 3.0, 5.0, 2.0, 4.0]
KBA13_SEG_MITTELKLASSE: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA13_SEG_OBEREMITTELKLASSE: [4.0, 3.0, 2.0, 5.0, 1.0]
KBA13_SEG_OBERKLASSE: [3.0, 1.0, 0.0, 5.0, 4.0, 2.0]
KBA13_SEG_SONSTIGE: [4.0, 3.0, 5.0, 2.0, 1.0]
KBA13_SEG_SPORTWAGEN: [4.0, 3.0, 5.0, 1.0, 0.0, 2.0]
KBA13_SEG_UTILITIES: [4.0, 3.0, 5.0, 2.0, 1.0]
KBA13_SEG_VAN: [4.0, 3.0, 2.0, 1.0, 5.0]
KBA13_SEG_WOHNMOBILE: [4.0, 3.0, 5.0, 2.0, 0.0, 1.0]
KBA13_SITZE_4: [3.0, 4.0, 5.0, 2.0, 1.0]
KBA13_SITZE_5: [1.0, 3.0, 2.0, 4.0, 5.0]
KBA13_SITZE_6: [5.0, 3.0, 4.0, 2.0, 1.0]
KBA13_TOYOTA: [2.0, 3.0, 5.0, 4.0, 1.0]
KBA13_VORB_0: [4.0, 3.0, 5.0, 2.0, 1.0]
KBA13_VORB_1: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA13_VORB_1_2: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA13_VORB_2: [3.0, 2.0, 4.0, 1.0, 5.0]
KBA13_VORB_3: [1.0, 3.0, 4.0, 0.0, 2.0, 5.0]
KBA13_VW: [4.0, 3.0, 1.0, 2.0, 5.0]
KONSUMNAEHE: [5.0, 1.0, 2.0, 4.0, 3.0, 6.0, 7.0]
MOBI_REGIO: [4.0, 5.0, 3.0, 1.0, 2.0, 6.0]
ONLINE_AFFINITAET: [3.0, 2.0, 1.0, 5.0, 4.0, 0.0]
ORTSGR_KLS9: [2.0, 5.0, 3.0, 7.0, 4.0, 8.0, 6.0, 1.0, 9.0]
PLZ8_ANTG1: [3.0, 2.0, 1.0, 4.0, 0.0]
PLZ8_ANTG2: [3.0, 2.0, 4.0, 1.0, 0.0]
PLZ8_ANTG3: [1.0, 3.0, 2.0, 0.0]
PLZ8_ANTG4: [0.0, 1.0, 2.0]
PLZ8_GBZ: [5.0, 3.0, 2.0, 4.0, 1.0]
PLZ8_HHZ: [5.0, 3.0, 4.0, 2.0, 1.0]
REGIOTYP: [1.0, 6.0, 7.0, 3.0, 4.0, 2.0, 5.0, 0.0]
RELAT_AB: [1.0, 3.0, 5.0, 2.0, 4.0, 9.0]
RETOURTYP_BK_S: [5.0, 3.0, 2.0, 4.0, 1.0]
RT_KEIN_ANREIZ: [1.0, 4.0, 3.0, 2.0, 5.0]
RT_SCHNAEPPCHEN: [5.0, 3.0, 2.0, 1.0, 4.0]
RT_UEBERGROESSE: [3.0, 2.0, 1.0, 5.0, 4.0]
SEMIO_DOM: [1.0, 3.0, 5.0, 6.0, 2.0, 4.0, 7.0]
SEMIO_ERL: [3.0, 7.0, 4.0, 6.0, 2.0, 5.0, 1.0]
SEMIO_FAM: [5.0, 6.0, 2.0, 4.0, 3.0, 1.0, 7.0]
SEMIO_KAEM: [1.0, 2.0, 6.0, 3.0, 7.0, 4.0, 5.0]
SEMIO_KRIT: [3.0, 7.0, 5.0, 1.0, 6.0, 2.0, 4.0]
SEMIO_KULT: [4.0, 1.0, 5.0, 6.0, 7.0, 2.0, 3.0]
SEMIO_LUST: [7.0, 5.0, 6.0, 4.0, 2.0, 1.0, 3.0]
SEMIO_MAT: [6.0, 3.0, 4.0, 1.0, 2.0, 5.0, 7.0]
SEMIO_PFLICHT: [2.0, 4.0, 3.0, 5.0, 1.0, 7.0, 6.0]
SEMIO_RAT: [1.0, 2.0, 3.0, 5.0, 4.0, 6.0, 7.0]
SEMIO_REL: [2.0, 1.0, 3.0, 4.0, 5.0, 6.0, 7.0]
SEMIO_SOZ: [6.0, 3.0, 2.0, 4.0, 5.0, 7.0, 1.0]
SEMIO_TRADV: [1.0, 4.0, 3.0, 2.0, 5.0, 6.0, 7.0]
SEMIO_VERT: [6.0, 7.0, 3.0, 5.0, 2.0, 4.0, 1.0]
W_KEIT_KIND_HH: [6.0, 2.0, 4.0, 1.0, 5.0, 3.0]
WOHNDAUER_2008: [9.0, 3.0, 7.0, 8.0, 5.0, 6.0, 4.0, 2.0, 1.0]
ALTERSKATEGORIE_GROB: [4.0, 3.0, 2.0, 1.0]

ordi_customer = df_customers[df_cate['ordinal']]
ordi_azdias = df_azdias[df_cate['ordinal']]

pp.ProfileReport(ordi_customer)

FINANZ_SPARER is highly correlated with FINANZ_ANLEGER (ρ = 0.91465) Rejected
KBA13_HALTER_66 is highly correlated with KBA13_ALTERHALTER_61 (ρ = 0.93218) Rejected
KBA13_HERST_SONST is highly correlated with KBA13_FAB_SONSTIGE (ρ = 1) Rejected
KBA13_KMH_250 is highly correlated with KBA13_KMH_211 (ρ = 0.96209) Rejected
PLZ8_GBZ is highly correlated with KBA13_GBZ (ρ = 0.97099) Rejected
PLZ8_HHZ is highly correlated with KBA13_HHZ (ρ = 0.96257) Rejected

pp.ProfileReport(ordi_azdias)

KBA13_HALTER_66 is highly correlated with KBA13_ALTERHALTER_61 (ρ = 0.92795) Rejected
KBA13_HERST_SONST is highly correlated with KBA13_FAB_SONSTIGE (ρ = 1) Rejected
KBA13_KMH_250 is highly correlated with KBA13_KMH_211 (ρ = 0.96077) Rejected
PLZ8_ANTG1 is highly correlated with KBA13_ANTG1 (ρ = 0.91196) Rejected
PLZ8_ANTG3 is highly correlated with KBA13_ANTG3 (ρ = 0.90311) Rejected
PLZ8_ANTG4 is highly correlated with KBA13_ANTG4 (ρ = 0.90186) Rejected
PLZ8_GBZ is highly correlated with KBA13_GBZ (ρ = 0.97064) Rejected
PLZ8_HHZ is highly correlated with KBA13_HHZ (ρ = 0.95882) Rejected

corr_ordi_columns = ['FINANZ_SPARER','KBA13_HALTER_66','KBA13_HERST_SONST','KBA13_KMH_250',
                     'PLZ8_GBZ','PLZ8_HHZ', 'PLZ8_ANTG1','PLZ8_ANTG3','PLZ8_ANTG4']

df_azdias.drop(columns=corr_ordi_columns, inplace=True)
df_customers.drop(columns=corr_ordi_columns, inplace=True)

for col in corr_ordi_columns:
    print(col)
    features.drop(features[features.attribute==col].index, inplace=True)
features.shape

FINANZ_SPARER
KBA13_HALTER_66
KBA13_HERST_SONST
KBA13_KMH_250
PLZ8_GBZ
PLZ8_HHZ
PLZ8_ANTG1
PLZ8_ANTG3
PLZ8_ANTG4

(270, 5)

df_customers.to_csv('clean_customers_drop_ordinal_correlated.csv', index=False)
df_azdias.to_csv('clean_azdias__drop_ordinal_correlated.csv', index=False)

Categorical¶

# Columns that are tagged as "categorical" are either binary or multiple
# Printing and tracking binary and multiple categorical values 
binary_cates = []
multi_cates = []
for col in df_customers[df_cate['categorical']]:
    values = df_customers[df_cate['categorical']][col].nunique()
    if values == 2:
        binary_cates.append(col)
    else:
        multi_cates.append(col)
    print(f'{col}: {values}')

AKT_DAT_KL: 9
CAMEO_DEU_2015: 44
CAMEO_DEUG_2015: 9
CJT_GESAMTTYP: 6
CJT_KATALOGNUTZER: 5
CJT_TYP_1: 5
CJT_TYP_2: 5
CJT_TYP_3: 5
CJT_TYP_4: 5
CJT_TYP_5: 5
CJT_TYP_6: 5
DSL_FLAG: 2
FINANZTYP: 6
FIRMENDICHTE: 5
GEBAEUDETYP: 6
GEMEINDETYP: 7
GFK_URLAUBERTYP: 12
GREEN_AVANTGARDE: 2
HH_DELTA_FLAG: 2
KOMBIALTER: 4
KONSUMZELLE: 2
LP_FAMILIE_FEIN: 11
LP_FAMILIE_GROB: 5
LP_STATUS_FEIN: 10
LP_STATUS_GROB: 5
MOBI_RASTER: 6
NATIONALITAET_KZ: 4
OST_WEST_KZ: 2
SHOPPER_TYP: 4
STRUKTURTYP: 3
UMFELD_ALT: 5
UMFELD_JUNG: 5
UNGLEICHENN_FLAG: 2
VERS_TYP: 2
VK_DHT4A: 11
VK_DISTANZ: 13
VK_ZG11: 11
WOHNLAGE: 7
ZABEOTYP: 6
ANREDE_KZ: 2

for col in binary_cates:
    print(df_customers[col].value_counts())

1.0    188421
0.0      3231
Name: DSL_FLAG, dtype: int64
0    121283
1     70369
Name: GREEN_AVANTGARDE, dtype: int64
0.0    171005
1.0     20647
Name: HH_DELTA_FLAG, dtype: int64
0.0    166546
1.0     25106
Name: KONSUMZELLE, dtype: int64
W    180309
O     11343
Name: OST_WEST_KZ, dtype: int64
0.0    179359
1.0     12293
Name: UNGLEICHENN_FLAG, dtype: int64
1.0    122610
2.0     69042
Name: VERS_TYP, dtype: int64
1.0    119508
2.0     72144
Name: ANREDE_KZ, dtype: int64

for col in binary_cates:
    print(df_azdias[col].value_counts())

1.0    865536
0.0     25685
Name: DSL_FLAG, dtype: int64
0    715996
1    175225
Name: GREEN_AVANTGARDE, dtype: int64
0.0    818544
1.0     72677
Name: HH_DELTA_FLAG, dtype: int64
0.0    702746
1.0    188475
Name: KONSUMZELLE, dtype: int64
W    722676
O    168545
Name: OST_WEST_KZ, dtype: int64
0.0    817571
1.0     73650
Name: UNGLEICHENN_FLAG, dtype: int64
2.0    509918
1.0    381303
Name: VERS_TYP, dtype: int64
2    465305
1    425916
Name: ANREDE_KZ, dtype: int64

# figure:

Part 1: Customer Segmentation Report¶

The main bulk of your analysis will come in this part of the project. Here, you should use unsupervised learning techniques to describe the relationship between the demographics of the company's existing customers and the general population of Germany. By the end of this part, you should be able to describe parts of the general population that are more likely to be part of the mail-order company's main customer base, and which parts of the general population are less so.

Part 2: Supervised Learning Model¶

Now that you've found which parts of the population are more likely to be customers of the mail-order company, it's time to build a prediction model. Each of the rows in the "MAILOUT" data files represents an individual that was targeted for a mailout campaign. Ideally, we should be able to use the demographic information from each individual to decide whether or not it will be worth it to include that person in the campaign.

The "MAILOUT" data has been split into two approximately equal parts, each with almost 43 000 data rows. In this part, you can verify your model with the "TRAIN" partition, which includes a column, "RESPONSE", that states whether or not a person became a customer of the company following the campaign. In the next part, you'll need to create predictions on the "TEST" partition, where the "RESPONSE" column has been withheld.

mailout_train = pd.read_csv('../../data/Term2/capstone/arvato_data/Udacity_MAILOUT_052018_TRAIN.csv', sep=';')

Part 3: Kaggle Competition¶

Now that you've created a model to predict which individuals are most likely to respond to a mailout campaign, it's time to test that model in competition through Kaggle. If you click on the link here, you'll be taken to the competition page where, if you have a Kaggle account, you can enter. If you're one of the top performers, you may have the chance to be contacted by a hiring manager from Arvato or Bertelsmann for an interview!

Your entry to the competition should be a CSV file with two columns. The first column should be a copy of "LNR", which acts as an ID number for each individual in the "TEST" partition. The second column, "RESPONSE", should be some measure of how likely each individual became a customer – this might not be a straightforward probability. As you should have found in Part 2, there is a large output class imbalance, where most individuals did not respond to the mailout. Thus, predicting individual classes and using accuracy does not seem to be an appropriate performance evaluation method. Instead, the competition will be using AUC to evaluate performance. The exact values of the "RESPONSE" column do not matter as much: only that the higher values try to capture as many of the actual customers as possible, early in the ROC curve sweep.

mailout_test = pd.read_csv('../../data/Term2/capstone/arvato_data/Udacity_MAILOUT_052018_TEST.csv', sep=';')

Number of variables	11
Number of observations	191652
Total Missing (%)	0.0%
Total size in memory	16.1 MiB
Average record size in memory	88.0 B

Numeric	9
Categorical	0
Boolean	0
Date	0
Text (Unique)	0
Rejected	2
Unsupported	0

Distinct count	216
Unique (%)	0.1%
Missing (%)	0.0%
Missing (n)	0
Infinite (%)	0.0%
Infinite (n)	0

Mean	3.9327
Minimum	0
Maximum	523
Zeros (%)	1.3%

Minimum	0
5-th percentile	1
Q1	1
Median	1
Q3	2
95-th percentile	14
Maximum	523
Range	523
Interquartile range	1

Capstone Project: Create a Customer Segmentation Report for Arvato Financial Services¶

data engineer¶

Overview

Variables

Correlations

Sample

Overview

Variables

Correlations

Sample

Drop highly correlated columns and impute zeros to median per columns¶

Re-Encoding¶

Ordinal¶

Overview

Variables

Correlations

Sample

Overview

Variables

Correlations

Sample

Categorical¶

Part 1: Customer Segmentation Report¶

Part 2: Supervised Learning Model¶

Part 3: Kaggle Competition¶

Standard deviation	12.428
Coef of variation	3.1601
Kurtosis	279.44
Mean	3.9327
MAD	4.3792
Skewness	13.856
Sum	753710
Variance	154.45
Memory size	1.5 MiB

Value	Count	Frequency (%)
1.0	122657	64.0%
2.0	22269	11.6%
3.0	7774	4.1%
4.0	4597	2.4%
5.0	3637	1.9%
6.0	3406	1.8%
7.0	3139	1.6%
8.0	2819	1.5%
0.0	2450	1.3%
9.0	2421	1.3%
Other values (206)	16483	8.6%

Standard deviation	0.62362
Coef of variation	1.9428
Kurtosis	240.17
Mean	0.32098
MAD	0.44702
Skewness	9.9652
Sum	61517
Variance	0.3889
Memory size	1.5 MiB

Standard deviation	0.56691
Coef of variation	1.6367
Kurtosis	5.367
Mean	0.34637
MAD	0.47815
Skewness	1.8493
Sum	66382
Variance	0.32138
Memory size	1.5 MiB

Standard deviation	1.3264
Coef of variation	0.6769
Kurtosis	2.5124
Mean	1.9596
MAD	1.0226
Skewness	1.3231
Sum	375560
Variance	1.7594
Memory size	1.5 MiB

Standard deviation	0.4406
Coef of variation	1.704
Kurtosis	-0.47164
Mean	0.25856
MAD	0.384
Skewness	1.1518
Sum	49554
Variance	0.19413
Memory size	1.5 MiB

Distinct count	3035
Unique (%)	1.6%
Missing (%)	0.0%
Missing (n)	0
Infinite (%)	0.0%
Infinite (n)	0

Standard deviation	4083
Coef of variation	0.62326
Kurtosis	-1.1047
Mean	6551.1
MAD	3665.8
Skewness	-0.88201
Sum	1255500000
Variance	16671000
Memory size	1.5 MiB

Standard deviation	8.9955
Coef of variation	0.65532
Kurtosis	-1.5089
Mean	13.727
MAD	8.1463
Skewness	-0.38062
Sum	2630800
Variance	80.919
Memory size	1.5 MiB

Standard deviation	974.53
Coef of variation	0.97124
Kurtosis	-1.9962
Mean	1003.4
MAD	974.05
Skewness	-0.058143
Sum	192302224
Variance	949710
Memory size	1.5 MiB

Distinct count	1251
Unique (%)	0.7%
Missing (%)	0.0%
Missing (n)	0
Infinite (%)	0.0%
Infinite (n)	0

Standard deviation	414.6
Coef of variation	0.84792
Kurtosis	0.50883
Mean	488.97
MAD	327.5
Skewness	0.74766
Sum	93711000
Variance	171900
Memory size	1.5 MiB

	ANZ_HAUSHALTE_AKTIV	ANZ_HH_TITEL	ANZ_PERSONEN	ANZ_STATISTISCHE_HAUSHALTE	EINGEFUEGT_AM	EINGEZOGENAM_HH_JAHR	GEBURTSJAHR	KBA13_ANZAHL_PKW	MIN_GEBAEUDEJAHR
0	1.0	0.0	2.0	1.0	9454.0	23.0	0	1201.0	25.0
1	1.0	1.0	3.0	1.0	1.0	10.0	0	1.0	1.0
2	1.0	0.0	1.0	1.0	9456.0	21.0	0	433.0	25.0
3	0.0	1.0	0.0	1.0	9456.0	20.0	0	755.0	25.0
4	7.0	0.0	4.0	7.0	9454.0	20.0	1960	513.0	25.0

Number of variables	11
Number of observations	891221
Total Missing (%)	0.0%
Total size in memory	74.8 MiB
Average record size in memory	88.0 B

Standard deviation	14.847
Coef of variation	1.8939
Kurtosis	158.2
Mean	7.8392
MAD	7.0155
Skewness	9.2578
Sum	6986400
Variance	220.43
Memory size	6.8 MiB

Standard deviation	1.2705
Coef of variation	2.6939
Kurtosis	6.4561
Mean	0.47162
MAD	0.81519
Skewness	2.5735
Sum	420310
Variance	1.6142
Memory size	6.8 MiB

Standard deviation	1.1623
Coef of variation	2.4666
Kurtosis	4.4576
Mean	0.4712
MAD	0.77323
Skewness	2.4399
Sum	419940
Variance	1.3508
Memory size	6.8 MiB

Standard deviation	1.2714
Coef of variation	0.66392
Kurtosis	7.8689
Mean	1.915
MAD	1.016
Skewness	1.3182
Sum	1706700
Variance	1.6165
Memory size	6.8 MiB

Distinct count	5163
Unique (%)	0.6%
Missing (%)	0.0%
Missing (n)	0
Infinite (%)	0.0%
Infinite (n)	0

Minimum	4
5-th percentile	4
Q1	8111
Median	9454
Q3	9456
95-th percentile	9456
Maximum	9694
Range	9690
Interquartile range	1345

Standard deviation	3052.7
Coef of variation	0.38854
Kurtosis	1.9327
Mean	7856.7
MAD	2226
Skewness	-1.8696
Sum	7002100000
Variance	9318900
Memory size	6.8 MiB

Standard deviation	7.2259
Coef of variation	0.57778
Kurtosis	-1.3051
Mean	12.506
MAD	6.393
Skewness	0.010383
Sum	11146000
Variance	52.213
Memory size	6.8 MiB

Standard deviation	976.58
Coef of variation	0.88685
Kurtosis	-1.9415
Mean	1101.2
MAD	969.48
Skewness	-0.24036
Sum	981393433
Variance	953720
Memory size	6.8 MiB

Distinct count	1261
Unique (%)	0.1%
Missing (%)	0.0%
Missing (n)	0
Infinite (%)	0.0%
Infinite (n)	0

Standard deviation	376.24
Coef of variation	0.68832
Kurtosis	1.2911
Mean	546.61
MAD	285.65
Skewness	0.8916
Sum	487150000
Variance	141560
Memory size	6.8 MiB

	ANZ_HAUSHALTE_AKTIV	ANZ_HH_TITEL	ANZ_KINDER	ANZ_PERSONEN	ANZ_STATISTISCHE_HAUSHALTE	ANZ_TITEL	EINGEFUEGT_AM	EINGEZOGENAM_HH_JAHR	GEBURTSJAHR	KBA13_ANZAHL_PKW	MIN_GEBAEUDEJAHR
0	4.0	4.0	4.0	4.0	4.0	4.0	4.0	4.0	0	4.0	4.0
1	11.0	0.0	0.0	2.0	12.0	0.0	9456.0	13.0	1996	963.0	25.0
2	10.0	0.0	0.0	1.0	7.0	0.0	9454.0	17.0	1979	712.0	25.0
3	1.0	0.0	0.0	0.0	2.0	0.0	7559.0	19.0	1957	596.0	20.0
4	3.0	0.0	0.0	4.0	3.0	0.0	9454.0	13.0	1963	435.0	25.0

	ANZ_HAUSHALTE_AKTIV	ANZ_HH_TITEL	ANZ_KINDER	ANZ_PERSONEN	EINGEFUEGT_AM	EINGEZOGENAM_HH_JAHR	GEBURTSJAHR	KBA13_ANZAHL_PKW
0	1	1	1	2	9454	23	1	1201
1	1	1	1	3	1	10	1	1
2	1	1	1	1	9456	21	1	433
3	1	1	1	1	9456	20	1	755
4	7	1	1	4	9454	20	1960	513

	ANZ_HAUSHALTE_AKTIV	ANZ_HH_TITEL	ANZ_KINDER	ANZ_PERSONEN	EINGEFUEGT_AM	EINGEZOGENAM_HH_JAHR	GEBURTSJAHR	KBA13_ANZAHL_PKW
count	191652.000000	191652.000000	191652.000000	191652.000000	191652.000000	191652.000000	191652.000000	191652.000000
mean	3.945505	1.017318	1.036598	1.996869	6551.084215	13.726911	1003.878112	488.965385
std	12.424388	0.391572	0.238847	1.284214	4083.037493	8.995253	974.031324	414.603955
min	1.000000	1.000000	1.000000	1.000000	1.000000	-1.000000	1.000000	1.000000
25%	1.000000	1.000000	1.000000	1.000000	1.000000	2.000000	1.000000	1.000000
50%	1.000000	1.000000	1.000000	2.000000	9454.000000	16.000000	1926.000000	474.000000
75%	2.000000	1.000000	1.000000	3.000000	9456.000000	23.000000	1949.000000	725.000000
max	523.000000	23.000000	8.000000	21.000000	9456.000000	31.000000	2017.000000	2300.000000

	ANZ_HAUSHALTE_AKTIV	ANZ_HH_TITEL	ANZ_KINDER	ANZ_PERSONEN	EINGEFUEGT_AM	EINGEZOGENAM_HH_JAHR	GEBURTSJAHR	KBA13_ANZAHL_PKW
count	891221.000000	891221.000000	891221.000000	891221.000000	891221.000000	891221.000000	891221.000000	891221.000000
mean	7.868177	3.928644	3.753175	2.068101	7856.726267	12.507298	1102.939344	546.609620
std	14.835457	0.480085	0.783874	1.272445	3052.690829	7.224532	974.598089	376.241229
min	1.000000	1.000000	1.000000	1.000000	4.000000	-1.000000	4.000000	1.000000
25%	2.000000	4.000000	4.000000	1.000000	8111.000000	5.000000	4.000000	308.000000
50%	4.000000	4.000000	4.000000	2.000000	9454.000000	13.000000	1943.000000	503.000000
75%	9.000000	4.000000	4.000000	3.000000	9456.000000	19.000000	1970.000000	738.000000
max	595.000000	23.000000	11.000000	45.000000	9694.000000	117.000000	2017.000000	2300.000000

	ARBEIT	BALLRAUM	D19_KONSUMTYP_MAX	EWDICHTE	FINANZ_ANLEGER	FINANZ_HAUSBAUER	FINANZ_MINIMALIST	FINANZ_SPARER	FINANZ_UNAUFFAELLIGER	FINANZ_VORSORGER	...	SEMIO_MAT	SEMIO_PFLICHT	SEMIO_RAT	SEMIO_REL	SEMIO_SOZ	SEMIO_TRADV	SEMIO_VERT	W_KEIT_KIND_HH	WOHNDAUER_2008	ALTERSKATEGORIE_GROB
0	4.0	6.0	9.0	6.0	5.0	3.0	3.0	4.0	5.0	3.0	...	5.0	5.0	4.0	7.0	2.0	3.0	1.0	6.0	9.0	2
1	3.0	6.0	9.0	3.0	5.0	5.0	1.0	5.0	4.0	2.0	...	3.0	7.0	6.0	4.0	5.0	6.0	1.0	3.0	9.0	1
2	3.0	2.0	8.0	4.0	2.0	5.0	1.0	4.0	3.0	1.0	...	3.0	3.0	4.0	3.0	4.0	3.0	4.0	3.0	9.0	3
3	2.0	4.0	8.0	2.0	2.0	2.0	4.0	2.0	1.0	5.0	...	1.0	4.0	3.0	2.0	5.0	4.0	4.0	6.0	9.0	4
4	4.0	2.0	1.0	5.0	1.0	2.0	4.0	3.0	3.0	4.0	...	2.0	4.0	2.0	4.0	6.0	2.0	7.0	2.0	9.0	3